import baostock as bs
import pymysql

db = pymysql.Connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='',
    db='gupiao',
    charset='utf8'
)

# db = pymysql.Connect(
#     host='apiccr.megowx.com',
#     port=3306,
#     user='gupiao',
#     passwd='Aitrip123',
#     db='gupiao',
#     charset='utf8'
# )
cursor = db.cursor()

sql_dk = "INSERT INTO `fa_stock_profit` (`year`, `quarter`, `code`, `pubDate`, `statDate`, `roeAvg`," \
         " `npMargin`, `gpMargin`, `netProfit`, `epsTTM`, `MBRevenue`, `totalShare`, `liqaShare`, `NRTurnRatio`," \
         " `NRTurnDays`, `INVTurnRatio`, `INVTurnDays`, `CATurnRatio`, `AssetTurnRatio`, `YOYEquity`, " \
         "`YOYAsset`, `YOYNI`, `YOYEPSBasic`, `YOYPNI`, `currentRatio`, `quickRatio`, `cashRatio`, " \
         "`YOYLiability`, `liabilityToAsset`, `assetToEquity`, `CAToAsset`, `NCAToAsset`, `tangibleAssetToAsset`, " \
         "`ebitToInterest`, `CFOToOR`, `CFOToNP`, `CFOToGr`, `dupontROE`, `dupontAssetStoEquity`, `dupontAssetTurn`, " \
         "`dupontPnitoni`, `dupontNitogr`, `dupontTaxBurden`, `dupontIntburden`, `dupontEbittogr`) VALUES "

year0 = 2018
quarter0 = 1
# 查询最新的K线日期
try:
    sql0 = "SELECT year,  quarter FROM `fa_stock_profit` WHERE 1 ORDER BY id DESC LIMIT 1 "
    result = cursor.execute(sql0)
    db.commit()
    if result:
        data = cursor.fetchone()
        year0 = int(data[0])
        quarter0 = int(data[1]) + 1
        if quarter0 == 5:
            year0 += 1
            quarter0 = 1

except Exception as e:
    print(e)

profit_list = []
operation_list = []
growth_list = []
balance_list = []
cash_flow_list = []
dupont_list = []
yq_list = []


def save2DB():
    global cursor
    global db
    global sql_dk
    global profit_list, operation_list, growth_list, balance_list, cash_flow_list, dupont_list, yq_list

    if len(profit_list) < 1:
        return False

    sql = sql_dk

    try:
        for j in range(0, len(profit_list)):
            if j > 0:
                sql += ","

            str1 = "'" + str(yq_list[j][0]) + "', '" + str(yq_list[j][1]) + "'"
            for i in range(0, len(profit_list[j])):
                item = profit_list[j][i]
                if len(item) < 1:
                    item = "0"
                str1 += ",'" + item + "'"

            for i in range(3, len(operation_list[j])):
                item = operation_list[j][i]
                if len(item) < 1:
                    item = "0"
                str1 += ",'" + item + "'"

            for i in range(3, len(growth_list[j])):
                item = growth_list[j][i]
                if len(item) < 1:
                    item = "0"
                str1 += ",'" + item + "'"

            for i in range(3, len(balance_list[j])):
                item = balance_list[j][i]
                if len(item) < 1:
                    item = "0"
                str1 += ",'" + item + "'"

            for i in range(3, len(cash_flow_list[j])):
                item = cash_flow_list[j][i]
                if len(item) < 1:
                    item = "0"
                str1 += ",'" + item + "'"

            for i in range(3, len(dupont_list[j])):
                item = dupont_list[j][i]
                if len(item) < 1:
                    item = "0"
                str1 += ",'" + item + "'"

            sql += "(" + str1 + ")"

        sql = sql.replace('nan', 'null').replace('None', 'null').replace('none', 'null')
        cursor.execute(sql)
        db.commit()
    except Exception as e:
        print(e)
        print(sql)
        return False

    profit_list = []
    operation_list = []
    growth_list = []
    balance_list = []
    cash_flow_list = []
    dupont_list = []
    yq_list = []

    return True


def querydata(code):
    global profit_list, operation_list, growth_list, balance_list, cash_flow_list, dupont_list, yq_list

    global year0, quarter0

    year = year0
    quarter = quarter0
    try:
        while year < 2020:
            yq = (year, quarter)

            isOK = 0
            rs_profit = bs.query_profit_data(code=code, year=year, quarter=quarter)
            while (rs_profit.error_code == '0') & rs_profit.next():
                profit_list.append(rs_profit.get_row_data())
                yq_list.append(yq)
                isOK = 1

            if isOK == 1:
                rs_operation = bs.query_operation_data(code=code, year=year, quarter=quarter)
                while (rs_operation.error_code == '0') & rs_operation.next():
                    operation_list.append(rs_operation.get_row_data())

                rs_growth = bs.query_growth_data(code=code, year=year, quarter=quarter)
                while (rs_growth.error_code == '0') & rs_growth.next():
                    growth_list.append(rs_growth.get_row_data())

                rs_balance = bs.query_balance_data(code=code, year=year, quarter=quarter)
                while (rs_balance.error_code == '0') & rs_balance.next():
                    balance_list.append(rs_balance.get_row_data())

                rs_cash_flow = bs.query_cash_flow_data(code=code, year=year, quarter=quarter)
                while (rs_cash_flow.error_code == '0') & rs_cash_flow.next():
                    cash_flow_list.append(rs_cash_flow.get_row_data())

                rs_dupont = bs.query_dupont_data(code=code, year=year, quarter=quarter)
                while (rs_dupont.error_code == '0') & rs_dupont.next():
                    dupont_list.append(rs_dupont.get_row_data())

            quarter += 1
            if quarter > 4:
                quarter = 1
                year += 1
    except Exception as e:
        print(e)
        print(code + " " + str(year) + " " + str(quarter))

    return len(profit_list)


#### 登陆系统 ####
lg = bs.login()
# 显示登陆返回信息
print('login respond error_code:' + lg.error_code)
print('login respond  error_msg:' + lg.error_msg)

### 获取证券信息 ####
rs = bs.query_all_stock(day="2019-04-30")
print('query_all_stock respond error_code:' + rs.error_code)
print('query_all_stock respond  error_msg:' + rs.error_msg)

#### 打印结果集 ####
# sh.600000～sz.300773
num = 0
isFirst = 0

while (rs.error_code == '0') & rs.next():
    one = rs.get_row_data()
    if isFirst == 0 and one[0] == "sh.600000":
        isFirst = 1

    if isFirst == 1:
        result = querydata(one[0])
        save2DB()
        if result == 0:
            continue
        print('----Finish---' + str(num))
        num += 1
        if one[0] == "sz.300773":
            break
        # if num > 0:
        #     break


#### 登出系统 ####
bs.logout()

cursor.close()
db.close()

print('----OK---')
